package com.xs.common.excel;

import org.apache.commons.beanutils.PropertyUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.stream.Collectors;

/**
 * @author xs
 * date 2018/3/27 15:57
 */
public class ExcelUtil {

    private ExcelUtil() {

    }

    public static <T> ExcelImportResult<T> importExcelMore(File file, Class<?> pojoClass, ImportParams params) {
        ExcelImportResult importResult = new ExcelImportResult();
        boolean needVerfiy = params.isNeedVerfiy();
        if (needVerfiy) {
            if (!IExcelModel.class.isAssignableFrom(pojoClass)) {
                throw new IllegalArgumentException("启用导入校验，必须pojo必须实现IExcelModel接口！");
            }
        }
        Workbook book = null;
        try {
            FileInputStream fileInputStream = new FileInputStream(file);
            book = WorkbookFactory.create(fileInputStream);
        } catch (Exception e) {
            e.printStackTrace();
            importResult.setErrorMsg("读取导入的Excel文件出错！");
            return importResult;
        }


        //获取pojoClass中所有表头字段
        List<Field> fieldList = getMappedFiled(pojoClass, null, false);
        // 用于存放列的序号和field.
        Map<Integer, Field> fieldsMap = new HashMap<>();
        //
        for (Field field : fieldList) {
            ExcelField excelField = field.getAnnotation(ExcelField.class);
            //列下表0,1,2
            int colIndex = getExcelCol(excelField.column());
            // 设置类的私有字段属性可访问.
            field.setAccessible(true);
            fieldsMap.put(colIndex, field);
        }
        List correctList = new ArrayList<>();
        List errorList = new ArrayList<>();
        Sheet sheet = book.getSheetAt(params.getStartSheetIndex());
        if (sheet != null && sheet.getPhysicalNumberOfRows() > 0) {
            boolean titleOk = cheakExcelHead(sheet.getRow(params.getTitleRowIndex()), fieldsMap);
            if (!titleOk) {
                importResult.setErrorMsg("表头非法");
            } else {

                //用于检测自身重复
                Map<String,Integer>  repeatSelfMap =new HashMap<>();
                //解析数据
                for (int i = params.getStartSheetIndex() + 1; i < sheet.getPhysicalNumberOfRows(); i++) {
                    Row row = sheet.getRow(i);
                    if (row == null) {
                        continue;
                    }
                    Object entity = null;
                    try {
                        entity = pojoClass.newInstance();
                    } catch (InstantiationException e) {
                        e.printStackTrace();
                    } catch (IllegalAccessException e) {
                        e.printStackTrace();
                    }
                    StringBuilder excelError = new StringBuilder();
                    StringBuilder uniqueKey =new StringBuilder();
                    for (int j = 0; j < row.getLastCellNum(); j++) {
                        Cell cell = row.getCell(j);
                        if (cell == null) {
                            continue;
                        }
                        // Excel中解析的值
                        Object cellValue = getCellValue(cell);
                        //取出excel列对应的pojo属性
                        Field field = fieldsMap.get(j);
                        ExcelField excelField = field.getAnnotation(ExcelField.class);
                        Object entityMemberValue = null;
                        try {
                            entityMemberValue = getEntityMemberValue(field, cellValue);
                            if(excelField.isUnique()){
                                uniqueKey.append(entityMemberValue.toString());
                            }
                            PropertyUtils.setProperty(entity, field.getName(), entityMemberValue);
                        } catch (Exception e) {
                            e.printStackTrace();
                            excelError.append("第").append(i + 1).append("行 ").append(excelField.name()).append("格式有误、");
                        }
                    }
                    //检查bean属性是否有问题
                    if (StringUtils.isNotEmpty(ValidationUtils.validateError(entity))) {
                        excelError.append(ValidationUtils.validateError(entity));
                    }
                    //根据unique列，判断是否存在自身重复
                    if(StringUtils.isNotEmpty(uniqueKey.toString())){
                        if(repeatSelfMap.containsKey(uniqueKey.toString())){
                            excelError = excelError.append ("和excel中第") .append (repeatSelfMap.get(uniqueKey.toString()) ) .append("行重复");
                        }else{
                            repeatSelfMap.put(uniqueKey.toString(),row.getRowNum()+1);
                        }
                    }
                    if (StringUtils.isNotEmpty(excelError.toString())) {
                        if (needVerfiy) {
                            try {
                                PropertyUtils.setProperty(entity, IExcelModel.errorMsgPropName, excelError.toString());
                            } catch (IllegalAccessException | InvocationTargetException | NoSuchMethodException e) {
                                e.printStackTrace();
                            }
                        }
                        errorList.add(entity);
                    } else {
                        correctList.add(entity);
                    }
                }
                if (!errorList.isEmpty()) {
                    importResult.setVerfiyFail(true);
                    importResult.setFailList(errorList);
                    if (params.isNeedExportError()) {
                        Workbook errorExcel = createErrorExcel(errorList, pojoClass);
                        importResult.setFailWorkbook(errorExcel);
                    }
                }
                importResult.setCorrectList(correctList);
                return importResult;
            }
        } else {
            importResult.setErrorMsg("无有效数据");
        }
        return importResult;
    }


    /**
     * excel 动态导出
     *
     * @param exportParams
     * @param pojoClass
     * @param outputStream
     */
    public static <T> void exportExcel(ExportParams exportParams, Class<T> pojoClass, OutputStream outputStream) throws IOException {
        List<?> data = exportParams.getData();
        if (data != null && data.isEmpty()) {
            return;
        }
        List<Field> fieldList = getMappedFiled(pojoClass, null, false);
        List<String> includeColName = exportParams.getIncludeColName();
        //导出的属性
        List<Field> exportFieldList = new ArrayList<>();
        if (includeColName != null && includeColName.size() > 0) {
            for (Field field : fieldList) {
                ExcelField fieldAnnotation = field.getAnnotation(ExcelField.class);
                for (String colName : includeColName) {
                    if (fieldAnnotation.name().equals(colName)) {
                        exportFieldList.add(field);
                    }
                }
            }
        } else {
            exportFieldList = fieldList;
        }

        List<Field> sortFieldList = exportFieldList.stream().sorted((Comparator.comparingInt(field -> getExcelCol(field.getAnnotation(ExcelField.class).column())))).collect(Collectors.toList());
        Workbook workbook = creatWorkbookByFieldList(exportParams.getSheetName(), sortFieldList, data);
        workbook.write(outputStream);
        workbook.close();
    }

    /**
     * 生成导入错误的excel
     */
    private static <T> Workbook createErrorExcel(List<?> errorData, Class<T> pojoClass) {
        List<Field> fieldList = getMappedFiled(pojoClass, null, true);
        return creatWorkbookByFieldList("错误数据", fieldList, errorData);

    }

    private static <T> Workbook creatWorkbookByFieldList(String sheetName, List<Field> fieldList, List dataList) {
        XSSFWorkbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet(sheetName);
        //错误Excel样式
        CellStyle errorCellStyle = createErrorCellStyle(workbook);
        //添加表头
        XSSFCellStyle headStyle = getHeadStyle(workbook);
        Row rowTitle = sheet.createRow(0);
        for (int i = 0; i < fieldList.size(); i++) {
            Cell cell = rowTitle.createCell(i);
            cell.setCellStyle(headStyle);
            ExcelField annotation = fieldList.get(i).getAnnotation(ExcelField.class);
            String colName = "";
            if (annotation != null) {
                colName = fieldList.get(i).getAnnotation(ExcelField.class).name();
            } else if (IExcelModel.errorMsgPropName.equals(fieldList.get(i).getName())) {
                colName = IExcelModel.errorMsgColName;
            }
            cell.setCellValue(colName);
            //设置列宽自适应
            sheet.setColumnWidth(i, colName.getBytes().length * 2 * 256);
        }
        //一行表头
        int startIndex = 1;
        for (int i = 0; i < dataList.size(); i++) {
            T entity = (T) dataList.get(i);
            Row row = sheet.createRow(startIndex++);
            for (int j = 0; j < fieldList.size(); j++) {
                Cell cell = row.createCell(j);
                if (IExcelModel.errorMsgPropName.equals(fieldList.get(j).getName())) {
                    cell.setCellStyle(errorCellStyle);
                }
                Object value = null;
                try {
                    value = PropertyUtils.getProperty(entity, fieldList.get(j).getName());
                } catch (IllegalAccessException e) {
                    e.printStackTrace();
                } catch (InvocationTargetException e) {
                    e.printStackTrace();
                } catch (NoSuchMethodException e) {
                    e.printStackTrace();
                }
                if (value instanceof Integer || value instanceof Long || value instanceof BigDecimal) {
                    cell.setCellValue(Double.valueOf(value.toString()));
                } else if (value instanceof Date) {
                    String dateFormat = DateNewUtil.getDateFormat((Date) value, "yyyy-MM-dd");
                    cell.setCellValue(dateFormat);
                } else if (value instanceof String) {
                    cell.setCellValue(value.toString());
                }
            }
        }
        return workbook;
    }

    /*****************************************************************************************************************************************************************************************/

    /**
     * 检查表头
     *
     * @param row
     * @param fieldsMap
     * @return
     */

    private static boolean cheakExcelHead(Row row, Map<Integer, Field> fieldsMap) {
        boolean titleOk = true;
        for (Map.Entry<Integer, Field> entry : fieldsMap.entrySet()) {
            String titleColName = entry.getValue().getAnnotation(ExcelField.class).name();
            String cellValue = row.getCell(entry.getKey()).getStringCellValue();
            if (!titleColName.equals(cellValue)) {
                titleOk = false;
                break;
            }
        }
        return titleOk;
    }


    /**
     * 设置表头样式
     *
     * @param workbook
     */

    private static XSSFCellStyle getHeadStyle(XSSFWorkbook workbook) {
        XSSFFont headFont = workbook.createFont();
        headFont.setFontName("宋体");
        headFont.setFontHeightInPoints((short) 12);
        headFont.setBold(true);
        XSSFCellStyle headStyle = workbook.createCellStyle();
        headStyle.setFont(headFont);
        //设置边框
        headStyle.setBorderTop(BorderStyle.MEDIUM);
        headStyle.setBorderRight(BorderStyle.MEDIUM);
        headStyle.setBorderBottom(BorderStyle.MEDIUM);
        headStyle.setBorderLeft(BorderStyle.MEDIUM);
        XSSFColor color = new XSSFColor(new java.awt.Color(221, 217, 196));
        headStyle.setFillForegroundColor(color);// 设置背景色
        headStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        headStyle.setAlignment(HorizontalAlignment.CENTER);
        return headStyle;
    }

    /**
     * 设置错误单元格样式
     *
     * @param workbook
     */

    private static CellStyle createErrorCellStyle(Workbook workbook) {
        CellStyle errorCellStyle = workbook.createCellStyle();
        Font font = workbook.createFont();
        font.setColor(Font.COLOR_RED);
        errorCellStyle.setFont(font);
        return errorCellStyle;
    }

    /**
     * 得到实体类所有通过注解映射了数据表的字段
     * 递归调用
     */

    private static List<Field> getMappedFiled(Class clazz, List<Field> fields, boolean addErrorMsgField) {
        Field errMsgField = null;
        if (fields == null) {
            fields = new ArrayList<Field>();
        }
        Field[] allFields = clazz.getDeclaredFields();// 得到所有定义字段
        for (Field field : allFields) {
            if (field.isAnnotationPresent(ExcelField.class)) {
                fields.add(field);
            }
            if (addErrorMsgField && field.getName().equals(IExcelModel.errorMsgPropName)) {
                errMsgField = field;
            }
        }
        if (clazz.getSuperclass() != null && !clazz.getSuperclass().equals(Object.class)) {
            getMappedFiled(clazz.getSuperclass(), fields, false);
        }
        if (errMsgField != null) {
            fields.add(errMsgField);
        }
        return fields;
    }

    /**
     * 将EXCEL中A,B,C,D,E列映射成0,1,2,3,4
     *
     * @param col
     */

    private static int getExcelCol(String col) {
        col = col.toUpperCase();
        // 从-1开始计算,字母重1开始运算。这种总数下来算数正好相同。
        int count = -1;
        char[] cs = col.toCharArray();
        for (int i = 0; i < cs.length; i++) {
            count += (cs[i] - 64) * Math.pow(26, cs.length - 1 - i);
        }
        return count;
    }

    /**
     * 根据Excel表格中的数据判断类型得到值
     *
     * @param cell
     * @return
     * @see [类、类#方法、类#成员]
     */
    private static Object getCellValue(Cell cell) {
        if (cell == null) {
            return null;
        }

        Object value = null;
        switch (cell.getCellTypeEnum()) {
            case _NONE:
                break;
            case BLANK:
                break;
            case BOOLEAN:
                value = cell.getBooleanCellValue();
                break;
            case ERROR:
                System.out.print("ERROR(" + cell.getErrorCellValue() + ")");
                break;
            case FORMULA:
                FormulaEvaluator evaluator = cell.getSheet().getWorkbook().getCreationHelper().createFormulaEvaluator();
                // 会打印出原本单元格的公式
                System.out.print(cell.getCellFormula() + "\t");
                // NumberFormat nf = new DecimalFormat("#.#");
                // String value = nf.format(cell.getNumericCellValue());
                CellValue cellValue = evaluator.evaluate(cell);
                switch (cellValue.getCellTypeEnum()) {
                    case _NONE:
                        break;
                    case BLANK:
                        break;
                    case BOOLEAN:
                        value = cellValue.getBooleanValue();
                        break;
                    case ERROR:
                        System.out.print("ERROR(" + cell.getErrorCellValue() + ")");
                        break;
                    case NUMERIC:
                        value = cellValue.getNumberValue();
                        break;
                    case STRING:
                        value = cell.getStringCellValue();
                        break;
                    default:
                        break;
                }
                break;
            case NUMERIC:
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                    return sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
                } else {
                    value = cell.getNumericCellValue();
                }
                break;
            case STRING:
                value = cell.getStringCellValue();
                break;
            default:
                break;
        }
        return value;
    }

    /**
     * 据实体成员变量的类型得到成员变量的值
     *
     * @param field
     * @param cellValue excel 取出来的值
     * @return
     */
    private static Object getEntityMemberValue(Field field, Object cellValue) throws Exception {
        Object realValue = null;
        ExcelField excelField = field.getAnnotation(ExcelField.class);
        boolean hasDefaultValue = excelField.hasDefaultValue();
        String type = field.getType().getName();
        switch (type) {
            case "char":
            case "java.lang.Character":
            case "java.lang.String":
                realValue = cellValue.toString();
                break;
            case "java.util.Date":
                realValue = StringUtils.isBlank(cellValue.toString()) ? null : DateNewUtil.getDateByString(cellValue.toString(), "yyyy-MM-dd");
                break;
            case "int":
            case "java.lang.Integer":
                if (cellValue == null) {
                    if (hasDefaultValue) {
                        realValue = 0;
                    } else {
                        realValue = null;
                    }
                } else {
                    if (cellValue instanceof Double) {
                        DecimalFormat df = new DecimalFormat("######0"); //四色五入转换成整数
                        realValue = Integer.parseInt(df.format(cellValue));
                    } else {
                        throw new Exception("属性：" + field.getName() + " 类型转换失败！");
                    }
                }
                break;
            case "long":
            case "java.lang.Long":
                if (cellValue == null) {
                    if (hasDefaultValue) {
                        realValue = 0L;
                    } else {
                        realValue = null;
                    }
                } else {
                    if (cellValue instanceof Double) {
                        realValue = Math.round((Double) cellValue);
                    } else {
                        throw new Exception("属性：" + field.getName() + " 类型转换失败！");
                    }
                }
                break;
            case "java.lang.float":
            case "java.lang.Float":
                if (cellValue == null) {
                    if (hasDefaultValue) {
                        realValue = 0.0f;
                    } else {
                        realValue = null;
                    }
                } else {
                    if (cellValue instanceof Double) {
                        realValue = cellValue;
                    } else {
                        throw new Exception("属性：" + field.getName() + " 类型转换失败！");
                    }
                }
                break;
            case "double":
            case "java.lang.Double":
                if (cellValue == null) {
                    if (hasDefaultValue) {
                        realValue = 0.00d;
                    } else {
                        realValue = null;
                    }
                } else {
                    if (StringUtils.isNumeric(cellValue.toString())) {
                        realValue = cellValue;
                    }
                }
                break;
            case "java.math.BigDecimal":
                if (cellValue == null) {
                    if (hasDefaultValue) {
                        realValue = BigDecimal.ZERO;
                    } else {
                        realValue = null;
                    }
                } else {
                    if (cellValue instanceof Double) {
                        realValue = BigDecimal.valueOf(Double.parseDouble(cellValue.toString()));
                    } else {
                        throw new Exception("属性：" + field.getName() + " 类型转换失败！");
                    }
                }
                break;
            default:
                break;
        }
        return realValue;
    }
}
